package com.guan.jdbc;
/**
 * 数据库操作的封装类，查询方法，一种返回map方式，一种返回实体类，一种使用反射实现
 * @author Administrator
 *
 */

import com.guan.core.annotation.Bean;
import com.guan.core.entity.PageData;
import com.guan.jdbc.db.DBUtil;

import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SuppressWarnings("all")
@Bean("jdbcBaseDao")
public class JDBCBaseDao {
	/**
	 * 返回list map的方式
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> queryMapForList(String sql, List<Object> params) {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 源数据对象
			ResultSetMetaData rsm = rs.getMetaData();
			// 获取结果集有多少列
			int num = rsm.getColumnCount();
			while (rs.next()) {
				Map<String, Object> obj = new HashMap<String, Object>();
				for (int i = 1; i <= num; i++) {
					// 根据列的状态获取列名
					String columnLabel = rsm.getColumnLabel(i);
					obj.put(columnLabel, rs.getObject(i));
				}
				result.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		}
		return result;
	}
	/**
	 * 查询单个对象(返回map)
	 * @param sql
	 * @param params
	 * @return
	 */
	public Map<String, Object> queryMapForObject(String sql, List<Object> params) {
		Map<String, Object> result = new HashMap<String, Object>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 源数据对象
			ResultSetMetaData rsm = rs.getMetaData();
			int num = rsm.getColumnCount();
			if (rs.next()) {
				for (int i =1;i<=num ;i++) {
					// 根据列的状态获取列名
					String columnLabel = rsm.getColumnLabel(i);
					result.put(columnLabel, rs.getObject(i));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		}
		return result;
	}
	/**
	 * 将查询的列映射到实体类的通用方法(返回实体类list对象)
	 * 
	 * @return
	 */
	public <E> List<E> queryEntityForList(PRowMapper<E> mapper, String sql, List<Object> params) {
		List<E> result = new ArrayList<E>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 获取结果集有多少列
			int rownum = 0;
			while (rs.next()) {
				rownum++;
				result.add(mapper.mapperRow(rs, rownum));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		} 
		return result;
	}
	/**
	 * 返回实体类单体对象
	 * @param mapper
	 * @param sql
	 * @param params
	 * @return
	 */
	public <E> E queryEntityForObject(PRowMapper<E> mapper, String sql, List<Object> params) {
		E result = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 获取结果集有多少列
			int rownum = 0;
			if (rs.next()) {
				rownum++;
				result=mapper.mapperRow(rs, rownum);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		} 
		return result;
	}
	/**
	 * 将查询的列映射到实体类的通用方法(通过反射实现)
	 * 思路的实现，没有对类型进行判断
	 * @return
	 */
	public <E> List<E> queryForObject(Class<E> cls, String sql, List<Object> params) {
		List<E> result = new ArrayList<E>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 源数据对象
			ResultSetMetaData rsm = rs.getMetaData();
			// 获取结果集有多少列
			int num = rsm.getColumnCount();
			E e;
			Method method ;
			Method[] methods = cls.getMethods();
			Map<String, Method> property = new HashMap<String, Method>();
			//遍历方法，拿到方法名，全部大写，与数据库列名对比
			for(int i = 0;i<methods.length;i++) {
				method = methods[1];
				String methodName = method.getName();
				if(methodName.startsWith("set")) {
					property.put(methodName.substring(3).toUpperCase(), method);
				}
			}
			while (rs.next()) {
				//通过类对象，创造类的实列，必须有空构造
				e = cls.newInstance();
				for(int i = 1 ;i<=num ;i++) {
					//拿到列名
					String columnName = rsm.getColumnName(i);
					//对比截取的方法名与列名
					if(property.containsKey(columnName)) {
						method = property.get(columnName);
						method.invoke(e, rs.getObject(i));
					}
 				}
				result.add(e);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		} 
		return result;
	}
	/**
	 * 增删改   更新操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public int excuteUpdate(String sql,List<Object> params) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int result = 0;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			setParameters(preparedStatement, params);
			result = preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		}
		return result;
	}
	/**
	 * 设置预处理语句的sql参数
	 * @param p
	 * @param
	 */
	@SuppressWarnings("unused")
	private void setParameters(PreparedStatement p,List<Object> params) {
		if (params != null) {
			for (int i = 0; i < params.size(); i++) {
				// 通用设置参数
				try {
					p.setObject(i + 1, params.get(i));
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					throw new RuntimeException(e);
				}
			}
		}
	}

	/**
	 * 查询总数的方法
	 * @param sql
	 * @param params
	 * @return
	 */
	public int getTotal(String sql,List<Object> params){
		int re = 0;
		Connection connection = DBUtil.getConnection();
		try {
			PreparedStatement pr = connection.prepareStatement(sql);
			setParameters(pr,params);
			ResultSet resultSet = pr.executeQuery();
			if(resultSet.next()){
				re = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
		return re;
	}

	/**
	 * 返回list pageDate对象
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<PageData> queryPage(String sql,List<Object> params){
		List<PageData> result = new ArrayList<PageData>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			setParameters(preparedStatement, params);
			rs = preparedStatement.executeQuery();
			// 源数据对象
			ResultSetMetaData rsm = rs.getMetaData();
			// 获取结果集有多少列
			int num = rsm.getColumnCount();
			while (rs.next()) {
				PageData obj = new PageData();
				for (int i = 1; i <= num; i++) {
					// 根据列的状态获取列名
					String columnLabel = rsm.getColumnLabel(i);
					obj.put(columnLabel, rs.getObject(i));
				}
				result.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBUtil.close();
		}
		return result;
	}
}
